/**
 * 
 */
package com.placement.app.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.placement.app.model.Address;
import com.placement.app.model.ZipLocation;
import com.placement.app.util.AddressLookup;

/**
 * @author ns_nagar
 * 
 */
public class LocationHelperDAOImpl extends JdbcTemplate implements
		LocationHelperDAO {

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.placement.app.dao.LocationHelperDAO#loadStates()
	 */
	public HashMap<String, String> loadStates() {
		// TODO Auto-generated method stub

		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.placement.app.dao.LocationHelperDAO#loadZipcodes()
	 */
	public ArrayList<Long> loadZipcodes() {
		// TODO Auto-generated method stub
		ArrayList<Long> codes = new ArrayList<Long>();
		String sql = "select zipcode from " + getSchema() + "zipcodes ";
		logger.info(">>loadZipcodes>> SQL:" + sql);

		List list = queryForList(sql);
		Long zipcode = new Long(0L);
		Iterator itr = list.iterator();
		if (itr.hasNext()) {
			while (itr.hasNext()) {
				Map map = (Map) itr.next();
				if (map.get("ZIPCODE") != null) {
					zipcode = new Long(((BigDecimal) map.get("ZIPCODE"))
							.longValue());
				}
				codes.add(zipcode);
			}
		}
		return codes;
	}

	private String getSchema() {
		// TODO Auto-generated method stub
		return "jobs.";
	}

	public ZipLocation getZipCode(Address address) {
		if (address==null)
			return null;
		String city = address.getCity();
		String state = address.getState();
		if (state != null && state.length() > 0 && state.length() != 2)
			AddressLookup.getProperty(state);
		String sql = "select * from " + getSchema() + "zipcodes "
				+ " where state = '" + state + "' and city = '" + city + "' ";
		logger.info(">>loadZipcodes>> SQL:" + sql);

		List list = queryForList(sql);
		Long zipcode = new Long(0L);
		Iterator itr = list.iterator();
		ZipLocation loc = new ZipLocation();
		
		if (itr.hasNext()) {
			while (itr.hasNext()) {
				Map map = (Map) itr.next();
				if (map.get("ZIPCODE") != null) {
					loc.setZipcode((Integer) map.get("ZIPCODE"));
				}
				if (map.get("LATITUDE") != null) {
					loc.setLat( (Double)map.get("LATITUDE"));
				}
				if (map.get("LONGITUDE") != null) {
					loc.setLon((Double) map.get("LONGITUDE"));
				}
				loc.setState((String)map.get("STATE"));
				loc.setCity((String)map.get("CITY"));
				}
			
			//return loc;
		} else {
			loc.setState(state);
			loc.setCity(city);
		}
		return loc;
	}

	public ZipLocation getZiplocationByCode(Long location) {
		String sql = "select * from " + getSchema() + "zipcodes "
				+ " where zipcode = " + location ;
		logger.info(">>getZiplocationByCode>> SQL: " + sql);

		List list = queryForList(sql);
		Iterator itr = list.iterator();
		if (itr.hasNext()) {
			while (itr.hasNext()) {
				Map map = (Map) itr.next();
				ZipLocation loc = new ZipLocation();
				
				if (map.get("ZIPCODE") != null) {
					loc.setZipcode((Integer) map.get("ZIPCODE"));
				}
				if (map.get("LATITUDE") != null) {
					loc.setLat(((Double)map.get("LATITUDE")).doubleValue());
				}
				if (map.get("LONGITUDE") != null) {
					loc.setLon(((Double) map.get("LONGITUDE")).doubleValue());
				}
				loc.setState((String)map.get("STATE"));
				loc.setCity((String)map.get("CITY"));
				return loc;
			}
		}
		return null;

	}

}
